import datetime
import os
import re
import shutil

import openpyxl
from QuickTools import tools_pmc
import tqdm
import win32com.client


def 路径批量识别():
    root_path = r"\\192.168.70.101\19计划管理部\01.计划\9，小记录"
    path_list = []
    for ii in os.walk(root_path):
        for jj in ii[2]:
            # i[0]是当前文件夹的绝对路径，j是文件名
            path = os.path.join(ii[0], jj)
            if "工时" in path and "工单完成情况.xls" not in path and "工单完工情况.xls" not in path:
                path_list.append(path)
    return path_list


wb = openpyxl.load_workbook(tools_pmc.lj_wlxxb)
ws = wb.worksheets[0]
dict_pn1 = {}
dict_pn2 = {}
for i in range(2, ws.max_row + 1):
    dict_pn1[str(ws.cell(row=i, column=1).value)] = 0
    dict_pn2[str(ws.cell(row=i, column=1).value)] = 0

excel = win32com.client.DispatchEx('Excel.application')
excel.Visible = False
excel.DisplayAlerts = False

path_list = 路径批量识别()

print(path_list)
for path in tqdm.tqdm(path_list):
    print(path)
    e_wb = excel.Workbooks.Open(path)
    e_wb.SaveAs(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\Temp\1.xlsx", FileFormat=51)
    e_wb.Close()
    # shutil.move(path + "x",r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\Temp")
    wb = openpyxl.load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\Temp\1.xlsx")
    ws = wb.worksheets[0]
    print(ws.cell(1, 19).value)
    for i in range(2, ws.max_row):
        if str(ws.cell(row=i, column=1).value) in dict_pn1 and ws.cell(row=i, column=19).value != 0:
            dict_pn1[str(ws.cell(row=i, column=1).value)] += ws.cell(row=i, column=19).value
            dict_pn2[str(ws.cell(row=i, column=1).value)] += 1
excel.Application.Quit()

print(1)
wb = openpyxl.Workbook()
ws = wb.worksheets[0]
a = 1
for i in dict_pn1:
    if dict_pn2[i] != 0:
        ws.cell(row=a, column=1).value = i
        ws.cell(row=a, column=2).value = dict_pn1[i] / dict_pn2[i]
    else:
        ws.cell(row=a, column=1).value = i
        ws.cell(row=a, column=2).value = 0
    a += 1
wb.save(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\Temp\2.xlsx")
